<?php 
/**
 *
 * @category    API
 * @package     Api_Album
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Album {

	/* Get objects by conditions
     * author: datnh
     */
	const table_name = 'tblvgt_album';
	
    function loadAlbums($limit, $start, &$total, $user, $password, $condition = array()) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$conds = '';
		if(!empty($condition)){
			foreach ($condition as $key=>$value) {
				$conds .= " AND  $key LIKE $value ";
			}
		}
		
        $sql = "SELECT COUNT(album_id) AS size FROM tblvgt_album WHERE 1=1 $conds";
        
        $stmt = sqlsrv_query($db, $sql);
        $obj = sqlsrv_fetch_object( $stmt);
        $total = $obj->size;
        
		DB_free_result($result);
		
		$sql = "SELECT * FROM(
				SELECT row_number() OVER (ORDER BY album_id) AS rownum, 
					album_id, 
					album_number ,
					album_name,
					album_author,					
					convert(varchar, created_date, 111) as created_date
				FROM tblvgt_album  WHERE 1=1 $conds) AS A	
				WHERE (A.rownum BETWEEN ($start) AND ($start + $limit)) ";	

		$result = DB_Query($sql, $db);
		
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
    }
	
	/* 
	* Insert a new object and return key
	* huudatxm
	*/
    function insert($data, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
                
        $FieldNames = '';
        $FieldValues = '';
        foreach ($data as $key=>$value) {
            $FieldNames .= $key.", ";
            $FieldValues .= 'N\''.$value.'\', ';
        }
		$FieldNames .= 'created_date, ';
        $FieldValues .= 'N\''.date('Y-m-d').'\', ';
		$FieldNames .= 'created_by, ';
        $FieldValues .= 'N\''.$_SESSION['UserID'].'\', ';
		$FieldNames .= 'updated_date, ';
        $FieldValues .= 'N\''.date('Y-m-d').'\', ';
		$FieldNames .= 'updated_by, ';
        $FieldValues .= 'N\''.$_SESSION['UserID'].'\', ';
		
        $sql = 'INSERT INTO tblvgt_album ('.substr($FieldNames, 0, -2).') '.' VALUES ('.substr($FieldValues, 0, -2).') ';
        if (sizeof($Errors) == 0) {
            DB_Query($sql, $db);
            return $_SESSION['LastInsertId'];	
        }
        return 0;
    }
	
	/* 
	* update 
	* huudatxm
	*/
    function update($data, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
		$val_str = $data['field']. ' = \''.$data['value'].'\'';
		$val_str .= ', updated_date = \''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = \''.$_SESSION['UserID'].'\' ';
        		
        $sql = 'UPDATE tblvgt_album SET '.$val_str;
		$sql .= ' WHERE album_id = '.$data['album_id'];
		
        if (sizeof($Errors) == 0) {			
            return DB_Query($sql, $db);
        }
        return 0;
    }
	
	/* 
	* delete 
	* huudatxm
	*/
    function delete($album_id, $user, $password) {        
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        $checkId = $this->verifyFieldExist('album_id',$album_id);
		if($checkId == 1) {
			$val_str = 'locked = 1';
			$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
			$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';	        		
	        $sql = "UPDATE ".self::table_name." SET $val_str WHERE album_id = $album_id";
			
	        if (sizeof($Errors) == 0) {			
	            return DB_Query($sql, $db);
	        }
		}
        return null;
    }
    
	/* Verify field existed.	 
     */
    function verifyFieldExist($field, $value) {
    	//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };
        $Searchsql = "SELECT count(*) as c FROM ".self::table_name." WHERE $field = N'$value'";
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            return 1;
        };
        return 0;
    }
    
	/**
	 * load datas with paginates
	 *
	 */
	function getAlbumPaginates($limit, $start, &$total, $conds) {
		//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };		
        //Get search conditions 
		$condsStr = null;
		if(!empty($conds)){
			$arr = array();
			foreach ($conds as $cond) {
				$arr[] = $cond['field'].' '.$cond['relation'].' '.$cond['value'];
			}
			$condsStr .= implode(" AND ", $arr);
		};		
		
		$where = '';
		if(!empty($condsStr)) {				
			$where = 'WHERE '.$condsStr;				
		};
		//Get query string for size
		$sql = "SELECT COUNT(*) AS size 
		FROM ".self::table_name." $where";		
		//Get size 
        $result = DB_Query($sql, $db);
        $obj = DB_fetch_object($result);
        $total = $obj->size;		        
		DB_free_result($result);		
		//Get query string with row_num
		$query = "SELECT row_number() OVER (ORDER BY album_id) AS rownum, 
					album_id, 
					album_number ,
					album_name,
					album_author,					
					convert(varchar, created_date, 111) as created_date					
				FROM ".self::table_name." $where";
				
		//Get query string with paginate
		$query = "SELECT * FROM(".$query.") AS A ";
		if(isset($start) && isset($limit)){
			$query .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
		}	
		//Execute query
        $result = DB_Query($query, $db);
		//Return result
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
	}
}

?>
